package com.sxfq.loansupermarket.backsystem.modular.report.service.impl;

import com.sxfq.loansupermarket.backsystem.core.util.StringUtil;
import com.sxfq.loansupermarket.backsystem.modular.report.entity.SearchWholeHospitalInfo;
import com.sxfq.loansupermarket.backsystem.modular.report.service.IGetTotleTwoNewService;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Service
@Transactional
public class GetTotleTwoNewServiceImpl implements IGetTotleTwoNewService {
    public String getsqlDepartment(SearchWholeHospitalInfo searchInfo, String classification, String condition, int i, List<SearchWholeHospitalInfo> list1) {
        String arrivalStore = "";
        if (StringUtil.isNotEmpty(searchInfo.getArrivalStore()) && !"请选择".equals(searchInfo.getArrivalStore())) {
            String arrivalStore2[] = searchInfo.getArrivalStore().split(",");
            if (arrivalStore2.length == 1) {
                arrivalStore = arrivalStore2[0];
            } else {
                for (int j = 0; j < arrivalStore2.length; j++) {
                    if (j == (arrivalStore2.length - 1)) {
                        arrivalStore += "'" + arrivalStore2[j];
                    } else if (j == 0) {
                        arrivalStore += arrivalStore2[j] + "'" + ",";
                    } else {
                        arrivalStore += "'" + arrivalStore2[j] + "'" + ",";
                    }
                }
            }
        }
        String sql = "select '" + classification + "' as rirstClassification," +
                "(select count(1) from (select * from (select b.customer_id from customer_status_statistics b where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.treatment_unit='" + classification + "' and b.customer_status='新客首次' and b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' group by b.reservations_id) t) t) AS outpatientVolume," +
                "(select count(1) from (select * from (select b.customer_id from customer_status_statistics b where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.treatment_unit='" + classification + "' and b.customer_status='新客首次' and b.isdeal_status='已成交' and b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' group by b.reservations_id) t) t)  AS volume," +
                "ifnull((select sum(t.section_performance) from (select a.section_performance from hospital_section_statistics a left join customer_status_statistics b on a.customer_id=b.customer_id where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.treatment_unit='" + classification + "' and b.customer_status='新客首次' and a.time>='" + searchInfo.getStartTime() + "' and a.time<='" + searchInfo.getEndTime() + "' group by a.id) t),0.00) as departmentPerformance from dual" +
                " union all " +
                " select '" + classification + "' as rirstClassification," +
                "(select count(1) from (select * from (select b.customer_id from customer_status_statistics b where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.treatment_unit='" + classification + "' and b.customer_status='新客多次' and b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' group by b.reservations_id) t) t) AS outpatientVolume," +
                "(select count(1) from (select * from (select b.customer_id from customer_status_statistics b where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.treatment_unit='" + classification + "' and (b.customer_status='新客多次' or b.customer_status='新客N次') and b.isdeal_status='已成交' and b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' group by b.reservations_id) t) t)  AS volume," +
                "ifnull((select sum(t.section_performance) from (select a.section_performance from hospital_section_statistics a left join customer_status_statistics b on a.customer_id=b.customer_id where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.treatment_unit='" + classification + "' and (b.customer_status='新客多次' or b.customer_status='新客N次') and a.time>='" + searchInfo.getStartTime() + "' and a.time<='" + searchInfo.getEndTime() + "' group by a.id) t),0.00) as departmentPerformance from dual" +
                " union all " +
                "select '" + classification + "' as rirstClassification," +
                "(select count(1) from (select * from (select b.customer_id from customer_status_statistics b where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.treatment_unit='" + classification + "' and b.customer_status in ('新客首次','新客多次') and b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' group by b.reservations_id) t) t) AS outpatientVolume," +
                "(select count(1) from (select * from (select b.customer_id from customer_status_statistics b where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.treatment_unit='" + classification + "' and b.customer_status in ('新客首次','新客多次','新客N次') and b.isdeal_status='已成交' and b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' group by b.reservations_id) t) t)  AS volume," +
                "ifnull((select sum(t.section_performance) from (select a.section_performance from hospital_section_statistics a left join customer_status_statistics b on a.customer_id=b.customer_id where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.treatment_unit='" + classification + "' and b.customer_status in ('新客首次','新客多次','新客N次') and a.time>='" + searchInfo.getStartTime() + "' and a.time<='" + searchInfo.getEndTime() + "' group by a.id) t),0.00) as departmentPerformance from dual" +
                " union all " +
                "select '" + classification + "' as rirstClassification," +
                "(select count(1) from (select * from (select b.customer_id from customer_status_statistics b where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.treatment_unit='" + classification + "' and b.customer_status='老客' and b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' group by b.reservations_id) t) t) AS outpatientVolume," +
                "(select count(1) from (select * from (select b.customer_id from customer_status_statistics b where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.treatment_unit='" + classification + "' and b.customer_status='老客' and b.isdeal_status='已成交' and b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' group by b.reservations_id) t) t)  AS volume," +
                "ifnull((select sum(t.section_performance) from (select a.section_performance from hospital_section_statistics a left join customer_status_statistics b on a.customer_id=b.customer_id where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.treatment_unit='" + classification + "' and b.customer_status='老客' and a.time>='" + searchInfo.getStartTime() + "' and a.time<='" + searchInfo.getEndTime() + "' group by a.id) t),0.00) as departmentPerformance from dual" +
                " union all " +
                "select '" + classification + "' as rirstClassification," +
                "(select count(1) from (select * from (select b.customer_id from customer_status_statistics b where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.treatment_unit='" + classification + "' and b.customer_status in ('新客首次','新客多次','老客') and b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' group by b.reservations_id) t) t) AS outpatientVolume," +
                "(select count(1) from (select * from (select b.customer_id from customer_status_statistics b where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.treatment_unit='" + classification + "' and b.customer_status in ('新客首次','新客多次','老客','新客N次') and b.isdeal_status='已成交' and b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' group by b.reservations_id) t) t)  AS volume," +
                "ifnull((select sum(t.section_performance) from (select a.section_performance from hospital_section_statistics a left join customer_status_statistics b on a.customer_id=b.customer_id where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.treatment_unit='" + classification + "' and b.customer_status in ('新客首次','新客多次','老客','新客N次') and a.time>='" + searchInfo.getStartTime() + "' and a.time<='" + searchInfo.getEndTime() + "' group by a.id) t),0.00) as departmentPerformance from dual";
        if (i == list1.size() - 1) {
        } else {
            sql += " union all ";
        }
        return sql;
    }

    public String getsqlDepartments(SearchWholeHospitalInfo searchInfo, String classification, String condition) {
        String arrivalStore = "";
        if (StringUtil.isNotEmpty(searchInfo.getArrivalStore()) && !"请选择".equals(searchInfo.getArrivalStore())) {
            String arrivalStore2[] = searchInfo.getArrivalStore().split(",");
            if (arrivalStore2.length == 1) {
                arrivalStore = arrivalStore2[0];
            } else {
                for (int j = 0; j < arrivalStore2.length; j++) {
                    if (j == (arrivalStore2.length - 1)) {
                        arrivalStore += "'" + arrivalStore2[j];
                    } else if (j == 0) {
                        arrivalStore += arrivalStore2[j] + "'" + ",";
                    } else {
                        arrivalStore += "'" + arrivalStore2[j] + "'" + ",";
                    }
                }
            }
        }
        String sql = "select '"+classification+"' as rirstClassification," +
                "(select count(1) from (select * from (select b.customer_id from customer_status_statistics b where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.treatment_unit='"+classification+"' and b.customer_status='新客首次' and " +
                " b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' group by b.reservations_id) t) t) AS outpatientVolume," +
                "(select count(1) from (select * from (select b.customer_id from customer_status_statistics b where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.treatment_unit='"+classification+"' and b.customer_status='新客首次' and b.isdeal_status='已成交' and " +
                " b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' group by b.reservations_id) t) t) AS volume," +
                "ifnull((select sum(t.section_performance) from (select a.section_performance from hospital_section_statistics a left join customer_status_statistics b on  a.customer_id=b.customer_id where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.treatment_unit='"+classification+"' and b.customer_status='新客首次' and a.time>='" + searchInfo.getStartTime() + "'  and a.time<='" + searchInfo.getEndTime() + "' group by a.id) t),0.00) AS departmentPerformance from dual " +
                " union all " +
                "select '"+classification+"' as rirstClassification," +
                "(select count(1) from (select * from (select b.customer_id from customer_status_statistics b where 1=1 and b.abbreviation in ('" + arrivalStore + "')  and b.treatment_unit='"+classification+"' and b.customer_status='新客多次' and " +
                " b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' group by b.reservations_id) t) t) AS outpatientVolume," +
                "(select count(1) from (select * from (select b.customer_id from customer_status_statistics b where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.treatment_unit='"+classification+"' and (b.customer_status='新客多次' or b.customer_status='新客N次') and b.isdeal_status='已成交' and " +
                " b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' group by b.reservations_id) t) t) AS volume," +
                "ifnull((select sum(t.section_performance) from (select a.section_performance from hospital_section_statistics a left join customer_status_statistics b on  a.customer_id=b.customer_id where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.treatment_unit='"+classification+"' and (b.customer_status='新客多次' or b.customer_status='新客N次') and a.time>='" + searchInfo.getStartTime() + "'  and a.time<='" + searchInfo.getEndTime() + "' group by a.id) t),0.00) AS departmentPerformance from dual " +
                " union all " +
                "select '"+classification+"' as rirstClassification," +
                "(select count(1) from (select * from (select b.customer_id from customer_status_statistics b where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.treatment_unit='"+classification+"' and b.customer_status in ('新客首次','新客多次') and " +
                " b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' group by b.reservations_id) t) t) AS outpatientVolume," +
                "(select count(1) from (select * from (select b.customer_id from customer_status_statistics b where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.treatment_unit='"+classification+"' and b.customer_status in ('新客首次','新客多次','新客N次') and b.isdeal_status='已成交' and " +
                " b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' group by b.reservations_id) t) t) AS volume," +
                "ifnull((select sum(t.section_performance) from (select a.section_performance from hospital_section_statistics a left join customer_status_statistics b on  a.customer_id=b.customer_id where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.treatment_unit='"+classification+"' and b.customer_status in ('新客首次','新客多次','新客N次') and a.time>='" + searchInfo.getStartTime() + "'  and a.time<='" + searchInfo.getEndTime() + "' group by a.id) t),0.00) AS departmentPerformance from dual " +
                " union all " +
                "select '"+classification+"' as rirstClassification," +
                "(select count(1) from (select * from (select b.customer_id from customer_status_statistics b where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.treatment_unit='"+classification+"' and b.customer_status='老客' and " +
                " b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' group by b.reservations_id) t) t) AS outpatientVolume," +
                "(select count(1) from (select * from (select b.customer_id from customer_status_statistics b where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.treatment_unit='"+classification+"' and b.customer_status='老客' and b.isdeal_status='已成交' and " +
                " b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' group by b.reservations_id) t) t) AS volume," +
                "ifnull((select sum(t.section_performance) from (select a.section_performance from hospital_section_statistics a left join customer_status_statistics b on  a.customer_id=b.customer_id where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.treatment_unit='"+classification+"' and b.customer_status='老客' and a.time>='" + searchInfo.getStartTime() + "'  and a.time<='" + searchInfo.getEndTime() + "' group by a.id) t),0.00) AS departmentPerformance from dual " +
                " union all " +
                "select '"+classification+"' as rirstClassification," +
                "(select count(1) from (select * from (select b.customer_id from customer_status_statistics b where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.treatment_unit='"+classification+"' and b.customer_status in ('新客首次','新客多次','老客') and " +
                " b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' group by b.reservations_id) t) t) AS outpatientVolume," +
                "(select count(1) from (select * from (select b.customer_id from customer_status_statistics b where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.treatment_unit='"+classification+"' and b.customer_status in ('新客首次','新客多次','老客','新客N次') and b.isdeal_status='已成交' and " +
                " b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' group by b.reservations_id) t) t) AS volume," +
                "ifnull((select sum(t.section_performance) from (select a.section_performance from hospital_section_statistics a left join customer_status_statistics b on  a.customer_id=b.customer_id where 1=1 and b.abbreviation in ('" + arrivalStore + "')and b.treatment_unit='"+classification+"' and b.customer_status in ('新客首次','新客多次','老客','新客N次') and a.time>='" + searchInfo.getStartTime() + "'  and a.time<='" + searchInfo.getEndTime() + "' group by a.id) t),0.00) AS departmentPerformance from dual ";
        return sql;
    }

    public String getTotleSqlDepartment(SearchWholeHospitalInfo searchInfo) {
        String arrivalStore = "";
        if (StringUtil.isNotEmpty(searchInfo.getArrivalStore()) && !"请选择".equals(searchInfo.getArrivalStore())) {
            String arrivalStore2[] = searchInfo.getArrivalStore().split(",");
            if (arrivalStore2.length == 1) {
                arrivalStore = arrivalStore2[0];
            } else {
                for (int j = 0; j < arrivalStore2.length; j++) {
                    if (j == (arrivalStore2.length - 1)) {
                        arrivalStore += "'" + arrivalStore2[j];
                    } else if (j == 0) {
                        arrivalStore += arrivalStore2[j] + "'" + ",";
                    } else {
                        arrivalStore += "'" + arrivalStore2[j] + "'" + ",";
                    }
                }
            }
        }
        String sql = " select '合计' as rirstClassification," +
                "(select count(1) from (select * from (select b.customer_id from customer_status_statistics b where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.customer_status='新客首次' and " +
                " b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' group by b.reservations_id) t) t) AS outpatientVolume," +
                "(select count(1) from (select * from (select b.customer_id from customer_status_statistics b where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.customer_status='新客首次' and b.isdeal_status='已成交' and " +
                " b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' group by b.reservations_id) t) t)  AS volume," +
                "ifnull((select sum(t.section_performance) from (select a.section_performance from hospital_section_statistics a left join customer_status_statistics b on a.customer_id=b.customer_id where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.customer_status='新客首次' and a.time>='" + searchInfo.getStartTime() + "' and a.time<='" + searchInfo.getEndTime() + "' group by a.id) t),0.00) as departmentPerformance from dual " +
                " union all " +
                " select '合计' as rirstClassification," +
                "(select count(1) from (select * from (select b.customer_id from customer_status_statistics b where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.customer_status='新客多次' and " +
                " b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' group by b.reservations_id) t) t) AS outpatientVolume," +
                "(select count(1) from (select * from (select b.customer_id from customer_status_statistics b where 1=1 and b.abbreviation in ('" + arrivalStore + "') and (b.customer_status='新客多次' or b.customer_status='新客N次') and b.isdeal_status='已成交' and " +
                " b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' group by b.reservations_id) t) t)  AS volume," +
                "ifnull((select sum(t.section_performance) from (select a.section_performance from hospital_section_statistics a left join customer_status_statistics b on a.customer_id=b.customer_id where 1=1 and b.abbreviation in ('" + arrivalStore + "') and (b.customer_status='新客多次' or b.customer_status='新客N次') and a.time>='" + searchInfo.getStartTime() + "' and a.time<='" + searchInfo.getEndTime() + "' group by a.id) t),0.00) as departmentPerformance from dual " +
                " union all " +
                " select '合计' as rirstClassification," +
                "(select count(1) from (select * from (select b.customer_id from customer_status_statistics b where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.customer_status in ('新客首次','新客多次') and " +
                " b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' group by b.reservations_id) t) t) AS outpatientVolume," +
                "(select count(1) from (select * from (select b.customer_id from customer_status_statistics b where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.customer_status in ('新客首次','新客多次','新客N次') and b.isdeal_status='已成交' and " +
                " b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' group by b.reservations_id) t) t)  AS volume," +
                "ifnull((select sum(t.section_performance) from (select a.section_performance from hospital_section_statistics a left join customer_status_statistics b on a.customer_id=b.customer_id where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.customer_status in ('新客首次','新客多次','新客N次') and a.time>='" + searchInfo.getStartTime() + "' and a.time<='" + searchInfo.getEndTime() + "' group by a.id) t),0.00) as departmentPerformance from dual " +
                " union all " +
                " select '合计' as rirstClassification," +
                "(select count(1) from (select * from (select b.customer_id from customer_status_statistics b where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.customer_status='老客' and " +
                " b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' group by b.reservations_id) t) t) AS outpatientVolume," +
                "(select count(1) from (select * from (select b.customer_id from customer_status_statistics b where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.customer_status='老客' and b.isdeal_status='已成交' and " +
                " b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' group by b.reservations_id) t) t)  AS volume," +
                "ifnull((select sum(t.section_performance) from (select a.section_performance from hospital_section_statistics a left join customer_status_statistics b on a.customer_id=b.customer_id where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.customer_status='老客' and a.time>='" + searchInfo.getStartTime() + "' and a.time<='" + searchInfo.getEndTime() + "' group by a.id) t),0.00) as departmentPerformance from dual " +
                " union all " +
                " select '合计' as rirstClassification," +
                "(select count(1) from (select * from (select b.customer_id from customer_status_statistics b where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.customer_status in ('新客首次','新客多次','老客') and " +
                " b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' group by b.reservations_id) t) t) AS outpatientVolume," +
                "(select count(1) from (select * from (select b.customer_id from customer_status_statistics b where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.customer_status in ('新客首次','新客多次','老客','新客N次') and b.isdeal_status='已成交' and " +
                " b.time>='" + searchInfo.getStartTime() + "' and b.time<='" + searchInfo.getEndTime() + "' group by b.reservations_id) t) t)  AS volume," +
                "ifnull((select sum(t.section_performance) from (select a.section_performance from hospital_section_statistics a left join customer_status_statistics b on a.customer_id=b.customer_id where 1=1 and b.abbreviation in ('" + arrivalStore + "') and b.customer_status in ('新客首次','新客多次','老客','新客N次') and a.time>='" + searchInfo.getStartTime() + "' and a.time<='" + searchInfo.getEndTime() + "' group by a.id) t),0.00) as departmentPerformance from dual ";
        return sql;
    }
}